Форум dkLab и Denwer
Здесь общаются Web-разработчики.
Генеральный спонсор:
Хостинг «Джино»

Решение проблемы pg_dump для SERIAL-полей, преобразованных в пользовательские SEQUENCE (Дмитрий Котеров)
Author Message
Дмитрий Котеров
Администратор



Joined: 10 Mar 2003
Posts: 13665
Карма: 413
   поощрить/наказать


PostPosted: Fri Jun 09, 2006 1:32 pm (написано за 17 минут 47 секунд)
   Post subject: Решение проблемы pg_dump для SERIAL-полей, преобразованных в пользовательские SEQUENCE
Reply with quote

Утилита pg_dump (дампер PostgreSQL) имеет одну очень неприятную особенность, описанную вот тут:
archives.postgresql.org/pgsql-general/2006-02/msg00251.php

Проблема заключается в том, что если мы изначально создали SERIAL-поле в таблицы, а затем поменяли его тип на другой, то pg_dump этого не понимает и по-прежнему продолжает считать его SERIAL-полем. Т.е. ALTER фактически срабатывает, и все работает, но вот дамп генерируется направильный.
  1. Предположим, мы создали таблицу с SERIAL-полем (автоинкрементное):
    Code (SQL): скопировать код в буфер обмена
    CREATE DATABASE testseq;
    CREATE TABLE test(id SERIAL, DATA TEXT);
    Дальше вставляем в нее некоторые данные:
    Code (SQL): скопировать код в буфер обмена
    INSERT INTO test(DATA) VALUES('aaa');
    INSERT INTO test(DATA) VALUES('bbb');
    Получаем для этих строк иентификаторы 1 и 2.

    Если теперь выполнить pg_dump для базы, все будет ОК: этот дамп можно восстановить в том же виде, в котором он был.
  2. Теперь вдруг мы решили, что последовательность 1, 2, 3 и т.д. нас не устраивает, а мы хотим более "случайную" последовательность (N * 1234567 % 1000). Никаких проблем: в PostgreSQL поддерживаются кастомные значения для DEFAULT-полей. Делаем:
    Code (SQL): скопировать код в буфер обмена
    ALTER TABLE test ALTER COLUMN id SET DEFAULT NEXTVAL('test_id_seq') * 1234567 % 1000;
    Можно легко убедиться, что команда отработала, и последовательность id действительно изменилась. Однако, если мы сделаем теперь дамп базы, то увидим, что поле id - по-прежнему SERIAL, а "DEFAULT nextval('test_id_seq') * 1234567 % 1000" потералось полностью. Естественно, при восстановлении такого дампа восстановится совсем не то, что было.
Решение проблемы пока что только одно: избегать SERIAL-полей при определении любых таблиц, а сразу же делать их типа integer, создавать для них SEQUENCE и привязывать явно default value:
Code (SQL): скопировать код в буфер обмена
CREATE SEQUENCE test_id_seq
    INCREMENT 1 MINVALUE 1
    MAXVALUE 9223372036854775807 START 2196
    CACHE 1;
CREATE TABLE test (
    id integer NOT NULL DEFAULT NEXTVAL('test_id_seq'::regclass),
    DATA TEXT
);
В этом, и только этом случае дамп+восстановление будет проходить корректно.

Что же делать, если уже есть на руках база с serial-полем? Тогда выход, опять же, только один.
  1. Делаем дамп структуры БД (pg_dump -s). Руками добавляем в него коменды CREATE SEQUENCE и заменяем описания вида "id SERIAL" на "id integer not null default nextval('*_seq'::regclass).
  2. Делаем дамп данных БД (pg_dump -a). Руками (или в автоматическом режиме) производим в нем такие изменения:
    Code (perl): скопировать код в буфер обмена
    #!/usr/bin/perl -w
    while (<>) {
        s (www.perldoc.com/perl5.6/pod/func/s.html)/pg_catalog\.pg_get_serial_sequence\('"?([^'"]+)"?', '"?([^'"]+)"?'\)/'$1_$2_seq'/s;
        print;
    }
    Иными словами, заменяем все строки вида
    Code (SQL): скопировать код в буфер обмена
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('A', 'b'), 21, true);
    на
    Code (SQL): скопировать код в буфер обмена
    SELECT pg_catalog.setval('A_b_seq', 21, true);
    Это нужно для того, чтобы инициализировать SEQUENCE и не потерять ее текущее значение.
  3. На новом месте восстанавливаем сначала откорректированный дамп структуры таблиц, а затем - откорректированный дамп данных. А чтобы восстановление дампа не затянулось на часы, необходимо вначале восстановить не всю структуру таблиц, а только ее часть, не включающую определение индексов и триггеров (там она идет в конце дампа структуры, легко отыскать). Т.е. вначале восстанавливаем структуру таблиц, потом - данные, а на последнем этапе - оставшийся кусок структуры (индексы и триггеры). Трехшаговый процесс в сотни раз быстрее (и, кстати, корректнее), чем отдельное восстановление структуры + данных.
К сожалению, процесс довольно-таки трудоемкий, но тут уж ничего не поделаешь.

P.S.
Насколько мне известно, разработчики PostgreSQL решили не исправлять этот баг в pg_dump, а просто запретить выполнять ALTER для SERIAL-полей. По-моему, это глупо.

Last edited by Дмитрий Котеров on Sun Jun 11, 2006 11:52 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail
Дмитрий Котеров
Администратор



Joined: 10 Mar 2003
Posts: 13665
Карма: 413
   поощрить/наказать


PostPosted: Sun Jun 11, 2006 11:48 pm (спустя 2 дня 10 часов 15 минут; написано за 52 секунды)
   Post subject:
Reply with quote

Чтобы было попроще, я написал скрипт, который делает все одним махом.
Теперь, имея "плохую" базу, можно быстро сделать из нее "хорошую".
Code (perl): скопировать код в буфер обмена
#!/usr/bin/perl -w
#
# pg_dump_repair_with_serial.pl: correct stupid PostgreSQL dump with
# SERIAL fields to dump with INTEGER fields and separate SEQUENCEs
# for each of these fields.
#
# SERIAL fields are bad because they cannot be ALTERed in the future.
# See http://archives.postgresql.org/pgsql-general/2006-02/msg00251.php
#
# Input: full SQL dump (structure + data) with SERIAL fields.
# Output: dump with INTEGER fields + CREATE SEQUENCE commands.
#
# Usage:
#
#   HOST=localhost
#   USER=<user>
#   SRC=<database-with-serial-fields>
#   DST=<resulting-database>
#
#   pg_dump -i -b -U $USER -h $HOST $SRC | perl repair_dump_with_serial.pl | gzip > $SRC-good.sql.gz
#   echo "drop database $DST; create database $DST;" | psql -U $USER -h $HOST -d $SRC
#   zcat $SRC-good.sql.gz | psql -U $USER -h $HOST -d $DST
#

my ($buffer, $nl) = ("", 0);
while (<>) {
        # One-line replacements.
        s (www.perldoc.com/perl5.6/pod/func/s.html)/pg_catalog\.pg_get_serial_sequence\('"?([^'"]+)"?', '"?([^'"]+)"?'\)/'$1_$2_seq'/s;
        # ...
       
        # Append current line to buffer.
        $buffer .= $_;
        $nl++;
       
        # Multi-line replacements.
        if (/\bserial\b/i) {
                $buffer =~ s{\b(CREATE TABLE "
?(\w+)"?[^;]+"?\b(\w+)\b"?) serial NOT NULL}{
                        "
CREATE SEQUENCE \"$2_$3_seq\"
                                INCREMENT 1
                                MINVALUE 1
                                MAXVALUE 9223372036854775807
                                START 1
                                CACHE 1;
                        $1 integer NOT NULL DEFAULT nextval('$2_$3_seq'::regclass)"

                }sie;
        }
        # ...
} continue {       
        if ($nl > 10) {
                print (www.perldoc.com/perl5.6/pod/func/print.html) $buffer;
                $buffer = "";
                $nl = 0;
        }
}

print (www.perldoc.com/perl5.6/pod/func/print.html) $buffer;
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic All times are GMT + 3 Hours
Page 1 of 1    Email to a Friend.
You cannot post new topics in this forum. You cannot reply to topics in this forum. You cannot edit your posts in this forum. You cannot delete your posts in this forum. You cannot vote in polls in this forum. You cannot attach files in this forum. You can download files in this forum.
XML